﻿using System;
using System.Collections.Generic;
using System.Collections.Concurrent;
using System.Threading.Tasks;
using System.IO;
using System.Linq;
using System.Text;
using SpreadsheetGear;
using MongoDB.Driver;
using Sustainalytics.ClientDW.Entities;
using Sustainalytics.DataLayer;
using Sustainalytics.UrlHelpers;

namespace Sustainalytics.CarbonData.BussinesLogic
{
    public class ExcelFileImport
    {
        private readonly string _connectionString;

        private const string SheetName = "Carbon Data";

        public ExcelFileImport(string connectionString)
        {
            _connectionString = connectionString;
        }

        private Dictionary<int, Guid> GetCorporateDataCompanyIds(IEnumerable<int> capitalIQIDs)
        {
            var hash = new HashSet<int>(capitalIQIDs);

            var corporateData = MongoDatabaseFactory
                .GetDatabase(_connectionString)
                .GetCollection<CorporateData>(typeof(CorporateData).Name)
                .Find(cd => hash.Contains(cd.CapitalIqId))
                .Project(cd => new
                {
                    cd.Id,
                    cd.CapitalIqId,
                    cd.PUCarbonData
                }).ToList();

            var dictionary = corporateData//.Where(cd => cd.PUCarbonData)
                .GroupBy(cd => cd.CapitalIqId)
                .ToDictionary(g => g.Key, v => v.Select(cd => cd.Id).First());

            return dictionary;
        }

        private static int ParseCapitalIqId(string capitalIqId)
        {
            if (string.IsNullOrEmpty(capitalIqId)) return 0;
            int parse;
            var input = new string(capitalIqId.Where(char.IsDigit).ToArray());
            int.TryParse(input, out parse);
            return parse;
        }

        private Dictionary<Guid, CorporateData> GetCorporateDatas(IEnumerable<Guid> companyIds)
        {
            var corporateData = MongoDatabaseFactory
                .GetDatabase(_connectionString)
                .GetCollection<CorporateData>(typeof(CorporateData).Name)
                .Find(cd => companyIds.Contains(cd.Id))
                .Project(cd => new CorporateData
                {
                    Id = cd.Id,
                    CompanyName = cd.CompanyName,
                    PUCarbonData = cd.PUCarbonData
                }).ToList();


            var dictionary = corporateData//.Where(cd => cd.PUCarbonData)
                .GroupBy(cd => cd.Id)
                .ToDictionary(g => g.Key, v => v.Select(cd => cd).First());

            return dictionary;
        }

        public ProcessResult Import(Stream stream)
        {
            var result = new ProcessResult() { Success = false };

            List<CompanyChange> companyChanges;
            var corporateDataDictionary = new Dictionary<Guid, CorporateData>();

            var validationResult = InternalValidation(stream, out companyChanges);

            result.Success = validationResult.Success;

            if (!result.Success) return result;


            var companyIds = companyChanges
                .Where(x => x.Error == InputValidationErrors.None)
                .Select(x => x.CompanyId)
                .ToList();


            var actions = new Action[]
            {
                () => corporateDataDictionary = GetCorporateDatas(companyIds),
                //todo load FinancialData and ReferenceData if is the case
            };

            var options = new ParallelOptions { MaxDegreeOfParallelism = Environment.ProcessorCount };

            var exceptions = new ConcurrentQueue<Exception>();
            Parallel.ForEach(actions, options, action =>
            {
                try
                {
                    action();
                }
                catch (Exception ex)
                {
                    exceptions.Enqueue(ex);
                }
            });
            if (exceptions.Count > 0)
            {
                var aex = (new AggregateException(exceptions)).Flatten();
                throw aex;
            }

            Parallel.ForEach(companyChanges.Where(x => x.Error == InputValidationErrors.None), options,
                (companyChange) =>
                {
                    var carbonData = new CarbonData()
                    {
                        Id = companyChange.CompanyId,
                        CIScope1GHGEmissions = companyChange.CIScope1GHGEmissions,
                        CIScope2GHGEmissions = companyChange.CIScope2GHGEmissions,
                        CarbonIntensity = companyChange.CarbonIntensity,
                        CarbonIntensityAssessmentType = companyChange.CarbonIntensityAssessmentType,
                        CarbonIntensityAssessment = companyChange.CarbonIntensityAssessment,
                    };

                    if (corporateDataDictionary.ContainsKey(companyChange.CompanyId))
                    {
                        var corporateData = corporateDataDictionary[companyChange.CompanyId];
                        carbonData.CompanyName = corporateData.CompanyName;
                        carbonData.CompanyNameLowerCase = corporateData.CompanyName.ToLowerInvariant();
                        var friendlyUrl = new UrlFriendlyGenerator();
                        carbonData.CompanyNameSafeUrl = friendlyUrl.GetFriendlyNameById(corporateData.Id, corporateData.CompanyName);

                        carbonData.UniverseIds = corporateData.UniverseIds;

                        //companyChange.Country = corporateData.Country;
                        //companyChange.Industry = corporateData.GICSIndustry;
                        //companyChange.PeerGroupName = corporateData.PeerGroupShortName != null
                        //    ? corporateData.PeerGroupShortName
                        //    : corporateData.RootPeerGroup;
                        //companyChange.Employees = corporateData.TotalEmployees;
                    }

                    //var fd = dwRepository.ListCompaniesFinancialData(new List<Guid>() { compChange.CompanyId }).FirstOrDefault();
                    //if (fd != null)
                    //{
                    //    compChange.MarketCap = fd.MarketCapitalization;
                    //}

                    //read ticker info
                    //var corpData = dwRepository.GetCorporateDataByCompanyId(compChange.CompanyId);
                    //if (corpData != null && !string.IsNullOrEmpty(corpData.MainExchange) && !string.IsNullOrEmpty(corpData.MainTicker))
                    //{
                    //    compChange.Exchange = corpData.MainExchange;
                    //    compChange.Ticker = corpData.MainTicker;
                    //}

                    //var l = new List<Guid>();
                    //l.Add(compChange.CompanyId);
                    //var refdata = dwRepository.ListCompaniesReferenceData(l).FirstOrDefault();

                    //else if(refdata != null && !string.IsNullOrEmpty(refdata.MainTicker))
                    //{
                    //    compChange.Exchange = refdata.MainExchange;
                    //    compChange.Ticker = refdata.MainTicker;
                    //}

                    if (companyChange.Operation == InputOperations.Create ||
                        companyChange.Operation == InputOperations.Update)
                    {
                        UpdateCarbonData(carbonData);
                    }
                    else if (companyChange.Operation == InputOperations.Delete)
                    {
                        RemoveCarbonData(carbonData);
                    }
                });

            return result;
        }

        private void UpdateCarbonData(CarbonData carbonData)
        {
            var collection = MongoDatabaseFactory
                .GetDatabase(_connectionString)
                .GetCollection<CarbonData>(typeof(CarbonData).Name);
            collection.UpdateOne(cd => cd.Id == carbonData.Id,
                Builders<CarbonData>.Update
                    .Set(cd => cd.CompanyName, carbonData.CompanyName)
                    .Set(cd => cd.CIScope1GHGEmissions, carbonData.CIScope1GHGEmissions)
                    .Set(cd => cd.CIScope2GHGEmissions, carbonData.CIScope2GHGEmissions)
                    .Set(cd => cd.CarbonIntensity, carbonData.CarbonIntensity)
                    .Set(cd => cd.CarbonIntensityAssessmentType, carbonData.CarbonIntensityAssessmentType)
                    .Set(cd => cd.CarbonIntensityAssessment, carbonData.CarbonIntensityAssessment),
                new UpdateOptions { IsUpsert = true });
        }

        private void RemoveCarbonData(CarbonData carbonData)
        {
            var collection = MongoDatabaseFactory
                .GetDatabase(_connectionString)
                .GetCollection<CarbonData>(typeof(CarbonData).Name);
            collection.UpdateOne(cd => cd.Id == carbonData.Id,
                Builders<CarbonData>.Update
                    .Set(cd => cd.IsDeleted, true),
                new UpdateOptions { IsUpsert = true });
        }


        public ValidationResult Validate(Stream stream)
        {
            List<CompanyChange> companyChanges;

            var validationResult = InternalValidation(stream, out companyChanges);

            var status = new StringBuilder();

            foreach (var companyChange in companyChanges)
            {
                companyChange.Details += (companyChange.Error == InputValidationErrors.None) ? "OK" : " Errors found";
                if (companyChange.Error != InputValidationErrors.None)
                    status.AppendLine(companyChange.Details);
            }

            validationResult.CompaniesInputDetails = status.ToString();
            validationResult.CompaniesInputSuccess = companyChanges.Any(c => c.Error == InputValidationErrors.None);

            var res = companyChanges.FirstOrDefault(c => c.Error != InputValidationErrors.None);
            if (res != null)
                validationResult.CompaniesInputError = res.Error;

            return validationResult;
        }


        private ValidationResult InternalValidation(Stream stream, out List<CompanyChange> companyChanges)
        {
            companyChanges = new List<CompanyChange>();

            var validationResult = new ValidationResult();

            var workbookSet = Factory.GetWorkbookSet(System.Globalization.CultureInfo.CurrentCulture);
            var workbook = workbookSet.Workbooks.OpenFromStream(stream);

            // search for the work sheets 
            var hasCarbonData = false;

            for (var i = 0; i < workbook.Sheets.Count; i++)
            {
                if (workbook.Sheets[i].Name == SheetName)
                    hasCarbonData = true;
            }

            if (!hasCarbonData)
            {
                validationResult.GeneralError = InputValidationErrors.NoSheetFound;
                validationResult.FrameworkError = InputValidationErrors.NoSheetFound;
                validationResult.FrameworkSuccess = false;
                validationResult.FrameworkDetails = "The file not have the \"Carbon Data\" sheet!";

                return validationResult;
            }

            CheckColumns(workbook, ref validationResult);
            if (validationResult.FrameworkError != InputValidationErrors.None)
                return validationResult;


            companyChanges = GetCompanyChanges(workbook);


            var capitalIQIDs = companyChanges
                .Select(cp => ParseCapitalIqId(cp.CapitalIQID))
                .Distinct()
                .ToArray();

            var corporateDataCompanyIds = GetCorporateDataCompanyIds(capitalIQIDs);

            foreach (var companyChange in companyChanges.Where(companyChange => companyChange.Error != InputValidationErrors.Other))
            {
                ValidateCompanyAssessment(companyChange, corporateDataCompanyIds);
            }

            validationResult.CompaniesInputSuccess = companyChanges.Any(c => c.Error == InputValidationErrors.None);

            return validationResult;
        }

        private void CheckColumns(IWorkbook workbook, ref ValidationResult validationResult)
        {
            //CD sheet does not contain 9 columns
            workbook.Sheets[SheetName].Select();
            var sheet = workbook.ActiveWorksheet;

            var error = InputValidationErrors.None;
            var details = new StringBuilder();

            var a = ReadCell(sheet, 'A', 1);
            if (!a.Contains("Action"))
            {
                error = InputValidationErrors.ColumnNotFound;
                details.AppendLine("Action column not found!");
            }

            var b = ReadCell(sheet, 'B', 1);
            if (!b.Contains("CAPIQ"))
            {
                error = InputValidationErrors.ColumnNotFound;
                details.AppendLine("CAPIQ column not found!");
            }

            var e = ReadCell(sheet, 'E', 1);
            if (!e.Contains("CI Scope 1 GHG Emissions"))
            {
                error = InputValidationErrors.ColumnNotFound;
                details.AppendLine("CI Scope 1 GHG Emissions column not found!");
            }

            var f = ReadCell(sheet, 'F', 1);
            if (!f.Contains("CI Scope 2 GHG Emissions"))
            {
                error = InputValidationErrors.ColumnNotFound;
                details.AppendLine("CI Scope 2 GHG Emissions column not found!");
            }

            var g = ReadCell(sheet, 'G', 1);
            if (!g.Contains("Carbon Intensity"))
            {
                error = InputValidationErrors.ColumnNotFound;
                details.AppendLine("Carbon Intensity column not found!");
            }

            var h = ReadCell(sheet, 'H', 1);
            if (!h.Contains("Reported / Estimated"))
            {
                error = InputValidationErrors.ColumnNotFound;
                details.AppendLine("Reported / Estimated column not found!");
            }

            var i = ReadCell(sheet, 'I', 1);

            if (!i.Contains("Carbon Intensity Assessment"))
            {
                error = InputValidationErrors.ColumnNotFound;
                details.AppendLine("Carbon Intensity Assessment column not found!");
            }

            if (error == InputValidationErrors.None) return;

            validationResult.FrameworkError = error;
            validationResult.FrameworkDetails = details.ToString();
        }

        private static string ReadCell(IWorksheet sheet, char column, int index)
        {

            var cell = sheet.Cells[column.ToString() + index.ToString()];

            switch (cell.ValueType)
            {
                case SpreadsheetGear.ValueType.Number:
                case SpreadsheetGear.ValueType.Text:
                    return (cell.Value == null) ? string.Empty : cell.Value.ToString();
                    break;
            }

            return string.Empty;
        }

        private static IEnumerable<Dictionary<char, string>> ReadCells(IWorksheet sheet)
        {
            var result = new List<Dictionary<char, string>>();
            var columns = new char[] { 'A', 'B', 'D', 'E', 'F', 'G', 'H', 'I' };
            var hasData = true;
            for (var row = 2; hasData; row++)
            {
                var k = new Dictionary<char, string>();
                foreach (var c in columns)
                {
                    k[c] = ReadCell(sheet, c, row);
                }

                hasData = !string.IsNullOrEmpty(k['B']);
                if (hasData)
                    result.Add(k);
            }
            return result;
        }

        private static List<CompanyChange> GetCompanyChanges(IWorkbook workbook)
        {
            var result = new List<CompanyChange>();

            workbook.Sheets[SheetName].Select();
            var sheet = workbook.ActiveWorksheet;


            var rows = ReadCells(sheet);

            var i = 2;

            foreach (var row in rows)
            {
                var companyChange = new CompanyChange();

                try
                {
                    switch (row['A'].ToLower())
                    {
                        case "add":
                            companyChange.Operation = InputOperations.Create;
                            break;
                        case "update":
                            companyChange.Operation = InputOperations.Update;
                            break;
                        case "delete":
                            companyChange.Operation = InputOperations.Delete;
                            break;
                        default:
                            companyChange.Operation = InputOperations.None;
                            break;
                    }

                    companyChange.CapitalIQID = row['B'];

                    companyChange.CIScope1GHGEmissions = -1;
                    if (!string.IsNullOrEmpty(row['E']))
                    {
                        int value;
                        if (int.TryParse(row['E'], out value))
                            companyChange.CIScope1GHGEmissions = value;
                    }

                    companyChange.CIScope2GHGEmissions = -1;
                    if (!string.IsNullOrEmpty(row['F']))
                    {
                        int value;
                        if (int.TryParse(row['F'], out value))
                            companyChange.CIScope2GHGEmissions = value;
                    }

                    companyChange.CarbonIntensity = -1;
                    if (!string.IsNullOrEmpty(row['G']))
                    {
                        int value;
                        if (int.TryParse(row['G'], out value))
                            companyChange.CarbonIntensity = value;
                    }

                    if (!string.IsNullOrEmpty(row['H']))
                    {
                        switch (row['H'].ToLower())
                        {
                            case "estimated":
                                companyChange.CarbonIntensityAssessmentType = CarbonIntensityAssessmentTypes.Estimated;
                                break;
                            case "reported":
                                companyChange.CarbonIntensityAssessmentType = CarbonIntensityAssessmentTypes.Reported;
                                break;
                            default:
                                companyChange.CarbonIntensityAssessmentType = CarbonIntensityAssessmentTypes.None;
                                break;
                        }
                    }

                    companyChange.CarbonIntensityAssessment = -1;
                    if (!string.IsNullOrEmpty(row['I']))
                    {
                        int value;
                        if (int.TryParse(row['I'], out value))
                            companyChange.CarbonIntensityAssessment = value;
                    }

                    companyChange.Details = "Sheet " + SheetName + ", Row: " + i.ToString();

                    companyChange.Error = InputValidationErrors.None;
                }
                catch (Exception)
                {
                    companyChange.Error = InputValidationErrors.Other;
                    companyChange.Details = "Unhandled exception";
                }

                result.Add(companyChange);

                i++;
            }

            return result;
        }


        public virtual void ValidateCompanyAssessment(CompanyChange companyChange, Dictionary<int, Guid> corporateDataCompanyIds)
        {
            companyChange.Details += ", CapitalIQId: " + companyChange.CapitalIQID + " |";

            if (companyChange.Operation == InputOperations.None)
            {
                companyChange.Details += "Operation unknown |";
                companyChange.Error = InputValidationErrors.OperationUnknown;
                return;
            }

            if (string.IsNullOrEmpty(companyChange.CapitalIQID))
            {
                companyChange.Details += "CapitalIQID is empty |";
                companyChange.Error = InputValidationErrors.CapitalIQIDEmpty;
                return;
            }

            //get companyId  based on companyChange.CapitalIQID;

            var capitalIQID = ParseCapitalIqId(companyChange.CapitalIQID);

            if (corporateDataCompanyIds.ContainsKey(capitalIQID))
            {
                var companyId = corporateDataCompanyIds[capitalIQID];

                companyChange.CompanyId = companyId;
            }
            else
            {
                companyChange.Details += "CapitalIQID not found |";
                companyChange.Error = InputValidationErrors.CapitalIQIDNotFound;
                return;
            }

            if (companyChange.CIScope1GHGEmissions < 0)
            {
                companyChange.Details += "CI Scope 1 GHG Emissions is invalid |";
                companyChange.Error = InputValidationErrors.CIScope1GHGEmissionsEmpty;
                return;
            }

            if (companyChange.CIScope2GHGEmissions < 0)
            {
                companyChange.Details += "CI Scope 2 GHG Emissions is invalid |";
                companyChange.Error = InputValidationErrors.CIScope2GHGEmissionsEmpty;
                return;
            }

            if (companyChange.CarbonIntensity < 0)
            {
                companyChange.Details += "Carbon Intensity is invalid |";
                companyChange.Error = InputValidationErrors.CarbonIntensityEmpty;
                return;
            }

            if (companyChange.CarbonIntensityAssessmentType == CarbonIntensityAssessmentTypes.None)
            {
                companyChange.Details += "Reported / Estimated is invalid |";
                companyChange.Error = InputValidationErrors.ReportedEstimatedEmpty;
                return;
            }

            if (companyChange.CarbonIntensityAssessment < 0 ||
                !(companyChange.CarbonIntensityAssessment >= 1 &&
                  companyChange.CarbonIntensityAssessment <= 5))
            {
                companyChange.Details += "Carbon Intensity Assessments is invalid |";
                companyChange.Error = InputValidationErrors.CarbonIntensityAssessmentEmpty;
            }

        }
    }
}